﻿'Description:工装工具车间基础数据类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh
'create Date:2022-2-24
'Rewriter:
'Rewrite Date:
Imports BusinessRule.DTO
Imports External.APICalling
Imports DatabaseAccess.DBUtility
Imports System.Text

Public Class GZBaseDataBusiness

#Region "产品处理"
    ''' <summary>
    ''' 创建产品
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateProduct(ByVal para As Dictionary(Of String, Object)) As uMESProcessResult

        Dim result As New uMESProcessResult

        Dim m_DataList As New List(Of ClientAPIEntity)
        m_DataList.Add(New ClientAPIEntity("Name", InputTypeEnum.Details, DataTypeEnum.DataField, para("ProductName"), ""))
        m_DataList.Add(New ClientAPIEntity("Revision", InputTypeEnum.Details, DataTypeEnum.DataField, para("ProductRev"), ""))
        m_DataList.Add(New ClientAPIEntity("Description", InputTypeEnum.Details, DataTypeEnum.DataField, para("ProductDesc"), ""))
        m_DataList.Add(New ClientAPIEntity("ProductType", InputTypeEnum.Details, DataTypeEnum.NamedObjectField, para("ProductType"), ""))
        If para.ContainsKey("BomName") Then
            If Not String.IsNullOrWhiteSpace("BomName") Then
                m_DataList.Add(New ClientAPIEntity("Bom", InputTypeEnum.Details, DataTypeEnum.RevisionedObjectField, para("BomName"), para("BomRev")))
            End If
        End If
        If para.ContainsKey("Materialtrademark") Then
            If Not String.IsNullOrWhiteSpace("Materialtrademark") Then
                m_DataList.Add(New ClientAPIEntity("Materialtrademark", InputTypeEnum.Details, DataTypeEnum.DataField, para("Materialtrademark"), ""))
            End If
        End If
        If para.ContainsKey("MaterialSpecification") Then
            If Not String.IsNullOrWhiteSpace("MaterialSpecification") Then
                m_DataList.Add(New ClientAPIEntity("MaterialSpecification", InputTypeEnum.Details, DataTypeEnum.DataField, para("MaterialSpecification"), ""))
            End If
        End If
        If para.ContainsKey("MaterialCode") Then
            If Not String.IsNullOrWhiteSpace("MaterialCode") Then
                m_DataList.Add(New ClientAPIEntity("MaterialCode", InputTypeEnum.Details, DataTypeEnum.DataField, para("MaterialCode"), ""))
            End If
        End If
        If para.ContainsKey("MaterialSize") Then
            If Not String.IsNullOrWhiteSpace("MaterialSize") Then
                m_DataList.Add(New ClientAPIEntity("MaterialSize", InputTypeEnum.Details, DataTypeEnum.DataField, para("MaterialSize"), ""))
            End If
        End If
        If para.ContainsKey("IsTopGz") Then 'add:Wangjh 是否是工装最顶层的件号
            If Not String.IsNullOrWhiteSpace("IsTopGz") Then
                m_DataList.Add(New ClientAPIEntity("IsTopGz", InputTypeEnum.Details, DataTypeEnum.DataField, para("IsTopGz"), ""))
            End If
        End If
        Dim serverUser As String = para("UserName")
        Dim serverPassword As String = para("Password")

        Dim api As New CamstarClientAPI(serverUser, serverPassword)

        Dim attrs As New Dictionary(Of String, Dictionary(Of String, List(Of ClientAPIEntity)))
        Dim attr As New Dictionary(Of String, List(Of ClientAPIEntity))
        If para.ContainsKey("TechnicianAndTime") Then
            If Not String.IsNullOrWhiteSpace("TechnicianAndTime") Then
                Dim list As New List(Of ClientAPIEntity)
                list.Add(New ClientAPIEntity("Name", InputTypeEnum.Details, DataTypeEnum.DataField, "工艺编制信息", ""))
                list.Add(New ClientAPIEntity("AttributeValue", InputTypeEnum.Details, DataTypeEnum.DataField, para("TechnicianAndTime"), ""))
                attr.Add(0, list)
                attrs.Add("Attributes", attr)
            End If
        End If

        result.Result = api.CreateModel("ProductMaint", m_DataList, attrs, result.Message)
        Return result

    End Function

    ''' <summary>
    ''' 创建产品版本
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateProductRev(ByVal para As Dictionary(Of String, Object)) As uMESProcessResult
        Dim result As New uMESProcessResult

        Dim m_DataList As New List(Of ClientAPIEntity)
        m_DataList.Add(New ClientAPIEntity("Name", InputTypeEnum.Details, DataTypeEnum.DataField, para("ProductName"), ""))
        m_DataList.Add(New ClientAPIEntity("Revision", InputTypeEnum.Details, DataTypeEnum.DataField, para("ProductRev"), ""))
        m_DataList.Add(New ClientAPIEntity("Description", InputTypeEnum.Details, DataTypeEnum.DataField, para("ProductDesc"), ""))
        m_DataList.Add(New ClientAPIEntity("ProductType", InputTypeEnum.Details, DataTypeEnum.NamedObjectField, para("ProductType"), ""))
        If para.ContainsKey("BomName") Then
            If Not String.IsNullOrWhiteSpace("BomName") Then
                m_DataList.Add(New ClientAPIEntity("Bom", InputTypeEnum.Details, DataTypeEnum.RevisionedObjectField, para("BomName"), para("BomRev")))
            End If
        End If
        If para.ContainsKey("Materialtrademark") Then
            If Not String.IsNullOrWhiteSpace("Materialtrademark") Then
                m_DataList.Add(New ClientAPIEntity("Materialtrademark", InputTypeEnum.Details, DataTypeEnum.DataField, para("Materialtrademark"), ""))
            End If
        End If
        If para.ContainsKey("MaterialSpecification") Then
            If Not String.IsNullOrWhiteSpace("MaterialSpecification") Then
                m_DataList.Add(New ClientAPIEntity("MaterialSpecification", InputTypeEnum.Details, DataTypeEnum.DataField, para("MaterialSpecification"), ""))
            End If
        End If
        If para.ContainsKey("MaterialCode") Then
            If Not String.IsNullOrWhiteSpace("MaterialCode") Then
                m_DataList.Add(New ClientAPIEntity("MaterialCode", InputTypeEnum.Details, DataTypeEnum.DataField, para("MaterialCode"), ""))
            End If
        End If
        If para.ContainsKey("MaterialSize") Then
            If Not String.IsNullOrWhiteSpace("MaterialSize") Then
                m_DataList.Add(New ClientAPIEntity("MaterialSize", InputTypeEnum.Details, DataTypeEnum.DataField, para("MaterialSize"), ""))
            End If
        End If
        If para.ContainsKey("IsTopGz") Then 'add:Wangjh 是否是工装最顶层的件号
            If Not String.IsNullOrWhiteSpace("IsTopGz") Then
                m_DataList.Add(New ClientAPIEntity("IsTopGz", InputTypeEnum.Details, DataTypeEnum.DataField, para("IsTopGz"), ""))
            End If
        End If
        Dim serverUser As String = para("UserName")
        Dim serverPassword As String = para("Password")

        Dim api As New CamstarClientAPI(serverUser, serverPassword)

        Dim attrs As New Dictionary(Of String, Dictionary(Of String, List(Of ClientAPIEntity)))
        Dim attr As New Dictionary(Of String, List(Of ClientAPIEntity))
        If para.ContainsKey("TechnicianAndTime") Then
            If Not String.IsNullOrWhiteSpace("TechnicianAndTime") Then
                Dim list As New List(Of ClientAPIEntity)
                list.Add(New ClientAPIEntity("Name", InputTypeEnum.Details, DataTypeEnum.DataField, "工艺编制信息", ""))
                list.Add(New ClientAPIEntity("AttributeValue", InputTypeEnum.Details, DataTypeEnum.DataField, para("TechnicianAndTime"), ""))
                attr.Add(0, list)
                attrs.Add("Attributes", attr)
            End If
        End If
        result.Result = api.CreateNewModelRevision("ProductMaint", para("ProductName"), para("OldProductRev"), m_DataList, attrs, result.Message)

        Return result
    End Function

    ''' <summary>
    ''' 更新产品
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateProduct(ByVal para As Dictionary(Of String, Object)) As uMESProcessResult

        Dim result As New uMESProcessResult

        Dim m_DataList As New List(Of ClientAPIEntity)
        m_DataList.Add(New ClientAPIEntity("Name", InputTypeEnum.Details, DataTypeEnum.DataField, para("ProductName"), ""))
        m_DataList.Add(New ClientAPIEntity("Revision", InputTypeEnum.Details, DataTypeEnum.DataField, para("ProductRev"), ""))
        m_DataList.Add(New ClientAPIEntity("Description", InputTypeEnum.Details, DataTypeEnum.DataField, para("ProductDesc"), ""))
        m_DataList.Add(New ClientAPIEntity("ProductType", InputTypeEnum.Details, DataTypeEnum.NamedObjectField, para("ProductType"), ""))
        If para.ContainsKey("BomName") Then
            If Not String.IsNullOrWhiteSpace("BomName") Then
                m_DataList.Add(New ClientAPIEntity("Bom", InputTypeEnum.Details, DataTypeEnum.RevisionedObjectField, para("BomName"), para("BomRev")))
            End If
        End If
        If para.ContainsKey("WorkflowName") Then
            If Not String.IsNullOrWhiteSpace("WorkflowName") Then
                m_DataList.Add(New ClientAPIEntity("Workflow", InputTypeEnum.Details, DataTypeEnum.RevisionedObjectField, para("WorkflowName"), para("WorkflowRev")))
            End If
        End If
        If para.ContainsKey("Materialtrademark") Then
            If Not String.IsNullOrWhiteSpace("Materialtrademark") Then
                m_DataList.Add(New ClientAPIEntity("Materialtrademark", InputTypeEnum.Details, DataTypeEnum.DataField, para("Materialtrademark"), ""))
            End If
        End If
        If para.ContainsKey("MaterialSpecification") Then
            If Not String.IsNullOrWhiteSpace("MaterialSpecification") Then
                m_DataList.Add(New ClientAPIEntity("MaterialSpecification", InputTypeEnum.Details, DataTypeEnum.DataField, para("MaterialSpecification"), ""))
            End If
        End If
        If para.ContainsKey("MaterialCode") Then
            If Not String.IsNullOrWhiteSpace("MaterialCode") Then
                m_DataList.Add(New ClientAPIEntity("MaterialCode", InputTypeEnum.Details, DataTypeEnum.DataField, para("MaterialCode"), ""))
            End If
        End If
        If para.ContainsKey("MaterialSize") Then
            If Not String.IsNullOrWhiteSpace("MaterialSize") Then
                m_DataList.Add(New ClientAPIEntity("MaterialSize", InputTypeEnum.Details, DataTypeEnum.DataField, para("MaterialSize"), ""))
            End If
        End If
        If para.ContainsKey("IsTopGz") Then 'add:Wangjh 是否是工装最顶层的件号
            If Not String.IsNullOrWhiteSpace("IsTopGz") Then
                m_DataList.Add(New ClientAPIEntity("IsTopGz", InputTypeEnum.Details, DataTypeEnum.DataField, para("IsTopGz"), ""))
            End If
        End If
        Dim serverUser As String = para("UserName")
        Dim serverPassword As String = para("Password")

        Dim api As New CamstarClientAPI(serverUser, serverPassword)

        Dim attrs As New Dictionary(Of String, Dictionary(Of String, List(Of ClientAPIEntity)))
        Dim attr As New Dictionary(Of String, List(Of ClientAPIEntity))
        If para.ContainsKey("TechnicianAndTime") Then
            If Not String.IsNullOrWhiteSpace("TechnicianAndTime") Then
                Dim list As New List(Of ClientAPIEntity)
                list.Add(New ClientAPIEntity("Name", InputTypeEnum.Details, DataTypeEnum.DataField, "工艺编制信息", ""))
                list.Add(New ClientAPIEntity("AttributeValue", InputTypeEnum.Details, DataTypeEnum.DataField, para("TechnicianAndTime"), ""))
                attr.Add(0, list)
                attrs.Add("Attributes", attr)
            End If
        End If
        result.Result = api.UpdateModel("ProductMaint", para("ProductName"), para("ProductRev"), m_DataList, attrs, result.Message)

        Return result

    End Function
#End Region

#Region "BOM处理"
    ''' <summary>
    ''' 创建BOM
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateBom(ByVal para As Dictionary(Of String, Object)) As uMESProcessResult

        Dim result As New uMESProcessResult

        Dim m_DataList As New List(Of ClientAPIEntity)
        m_DataList.Add(New ClientAPIEntity("Name", InputTypeEnum.Details, DataTypeEnum.DataField, para("BomName"), ""))
        m_DataList.Add(New ClientAPIEntity("Revision", InputTypeEnum.Details, DataTypeEnum.DataField, para("BomRev"), ""))
        m_DataList.Add(New ClientAPIEntity("Description", InputTypeEnum.Details, DataTypeEnum.DataField, para("BomDesc"), ""))

        Dim materialList As DataTable = para("MaterialList")
        Dim p_subEntityList As New Dictionary(Of String, Dictionary(Of String, List(Of ClientAPIEntity)))
        Dim subEntityList As New Dictionary(Of String, List(Of ClientAPIEntity))

        Dim bomSqlList As New ArrayList

        For i As Integer = 0 To materialList.Rows.Count - 1
            Dim singleMaterial As New List(Of ClientAPIEntity)
            singleMaterial.Add(New ClientAPIEntity("Product", InputTypeEnum.Details, DataTypeEnum.RevisionedObjectField, materialList.Rows(i)("ProductName"), materialList.Rows(i)("ProductRev")))
            singleMaterial.Add(New ClientAPIEntity("QtyRequired", InputTypeEnum.Details, DataTypeEnum.DataField, materialList.Rows(i)("QtyRequired"), ""))
            singleMaterial.Add(New ClientAPIEntity("Qty2Required", InputTypeEnum.Details, DataTypeEnum.DataField, materialList.Rows(i)("Qty2Required"), ""))
            'singleMaterial.Add(New ClientAPIEntity("TopProductName", InputTypeEnum.Details, DataTypeEnum.DataField, materialList.Rows(i)("TopProductName"), ""))
            'singleMaterial.Add(New ClientAPIEntity("TopProductRev", InputTypeEnum.Details, DataTypeEnum.DataField, materialList.Rows(i)("TopProductRev"), ""))
            singleMaterial.Add(New ClientAPIEntity("IssueControl", InputTypeEnum.Details, DataTypeEnum.DataField, "5", ""))
            subEntityList.Add(i.ToString, singleMaterial)

            bomSqlList.Add(SaveBomProductSubProduct(para("faProductName"), para("faProductRev"), materialList.Rows(i)))
        Next
        p_subEntityList.Add("MaterialList", subEntityList)

        Dim serverUser As String = para("UserName")
        Dim serverPassword As String = para("Password")

        Dim api As New CamstarClientAPI(serverUser, serverPassword)

        result.Result = api.CreateModel("WorkflowBOMMaint", m_DataList, p_subEntityList, result.Message)

        If result.Result Then
            DatabaseHelper.ExecuteSqlTran(bomSqlList)
        End If

        Return result

    End Function

    ''' <summary>
    ''' 保存bom的父子零件信息
    ''' </summary>
    ''' <param name="productName"></param>
    ''' <param name="productRev"></param>
    ''' <param name="drSub">子件号信息</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SaveBomProductSubProduct(ByVal productName As String, ByVal productRev As String, ByVal drSub As DataRow) As String
        Dim strSql = <string>select 1 from bomproductsubproduct pp where pp.productName='{0}' and pp.productRev='{1}' and pp.subProductName='{2}' and pp.subProductRev='{3}' </string>.Value
        'If DatabaseHelper.GetSingle(String.Format(strSql, productName, productRev, subProductName, subProductRev)) IsNot Nothing Then
        '    Return ""
        'End If
        strSql = <string>insert into bomproductsubproduct (productName,productRev,subProductName,subProductRev,requireQty,requireqty2,topproductname,Topproductrev,createdate,BomProductSubProductID) values ('{0}','{1}','{2}','{3}',{4},{5},'{6}','{7}',sysdate,sys_guid())</string>.Value

        Return String.Format(strSql, productName, productRev, drSub("ProductName").ToString, drSub("ProductRev").ToString, drSub("QtyRequired").ToString, drSub("Qty2Required").ToString, drSub("TopProductName").ToString, drSub("TopProductRev").ToString)

    End Function

    ''' <summary>
    ''' 创建BOM版本
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateBomRev(ByVal para As Dictionary(Of String, Object)) As uMESProcessResult
        Dim result As New uMESProcessResult

        Dim m_DataList As New List(Of ClientAPIEntity)
        m_DataList.Add(New ClientAPIEntity("Name", InputTypeEnum.Details, DataTypeEnum.DataField, para("BomName"), ""))
        m_DataList.Add(New ClientAPIEntity("Revision", InputTypeEnum.Details, DataTypeEnum.DataField, para("BomRev"), ""))
        m_DataList.Add(New ClientAPIEntity("Description", InputTypeEnum.Details, DataTypeEnum.DataField, para("BomDesc"), ""))

        Dim materialList As DataTable = para("MaterialList")
        Dim p_subEntityList As New Dictionary(Of String, Dictionary(Of String, List(Of ClientAPIEntity)))
        Dim subEntityList As New Dictionary(Of String, List(Of ClientAPIEntity))

        Dim bomSqlList As New ArrayList

        For i As Integer = 0 To materialList.Rows.Count - 1
            Dim singleMaterial As New List(Of ClientAPIEntity)
            singleMaterial.Add(New ClientAPIEntity("Product", InputTypeEnum.Details, DataTypeEnum.RevisionedObjectField, materialList.Rows(i)("ProductName"), materialList.Rows(i)("ProductRev")))
            singleMaterial.Add(New ClientAPIEntity("QtyRequired", InputTypeEnum.Details, DataTypeEnum.DataField, materialList.Rows(i)("QtyRequired"), ""))
            singleMaterial.Add(New ClientAPIEntity("Qty2Required", InputTypeEnum.Details, DataTypeEnum.DataField, materialList.Rows(i)("Qty2Required"), ""))
            singleMaterial.Add(New ClientAPIEntity("IssueControl", InputTypeEnum.Details, DataTypeEnum.DataField, "5", ""))
            subEntityList.Add(i.ToString, singleMaterial)

            bomSqlList.Add(SaveBomProductSubProduct(para("faProductName"), para("faProductRev"), materialList.Rows(i)))

        Next
        p_subEntityList.Add("MaterialList", subEntityList)

        Dim serverUser As String = para("UserName")
        Dim serverPassword As String = para("Password")

        Dim api As New CamstarClientAPI(serverUser, serverPassword)

        result.Result = api.CreateNewModelRevision("WorkflowBOMMaint", para("BomName"), para("OldBomRev"), m_DataList, p_subEntityList, result.Message)

        If result.Result Then
            DatabaseHelper.ExecuteSqlTran(bomSqlList)
        End If

        Return result
    End Function

    ''' <summary>
    ''' 更新BOM
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateBom(ByVal para As Dictionary(Of String, Object)) As uMESProcessResult

        Dim result As New uMESProcessResult

        Dim m_DataList As New List(Of ClientAPIEntity)
        m_DataList.Add(New ClientAPIEntity("Name", InputTypeEnum.Details, DataTypeEnum.DataField, para("BomName"), ""))
        m_DataList.Add(New ClientAPIEntity("Revision", InputTypeEnum.Details, DataTypeEnum.DataField, para("BomRev"), ""))
        m_DataList.Add(New ClientAPIEntity("Description", InputTypeEnum.Details, DataTypeEnum.DataField, para("BomDesc"), ""))

        Dim materialList As DataTable = para("MaterialList")
        Dim subEntityList As New Dictionary(Of String, List(Of ClientAPIEntity))

        Dim bomSqlList As New ArrayList

        For i As Integer = 0 To materialList.Rows.Count - 1
            Dim singleMaterial As New List(Of ClientAPIEntity)
            singleMaterial.Add(New ClientAPIEntity("Product", InputTypeEnum.Details, DataTypeEnum.RevisionedObjectField, materialList.Rows(i)("ProductName"), materialList.Rows(i)("ProductRev")))
            singleMaterial.Add(New ClientAPIEntity("QtyRequired", InputTypeEnum.Details, DataTypeEnum.DataField, materialList.Rows(i)("QtyRequired"), ""))
            singleMaterial.Add(New ClientAPIEntity("Qty2Required", InputTypeEnum.Details, DataTypeEnum.DataField, materialList.Rows(i)("Qty2Required"), ""))
            singleMaterial.Add(New ClientAPIEntity("IssueControl", InputTypeEnum.Details, DataTypeEnum.DataField, "5", ""))
            subEntityList.Add(i.ToString, singleMaterial)

            bomSqlList.Add(SaveBomProductSubProduct(para("faProductName"), para("faProductRev"), materialList.Rows(i)))

        Next
        '查询oldCount
        Dim strSql = <string>select count(1) n from productmateriallistitem pm     
               left join bom b on b.bomid=pm.bomid
               left join bombase bb on bb.bombaseid=b.bombaseid          
               where bb.bomname='{0}' and b.bomrevision='{1}' </string>.Value
        Dim oldCount As Integer = Val(DatabaseHelper.GetSingle(String.Format(strSql, para("BomName"), para("BomRev"))))

        Dim serverUser As String = para("UserName")
        Dim serverPassword As String = para("Password")

        Dim api As New CamstarClientAPI(serverUser, serverPassword)

        result.Result = api.UpdateModelHasSubentityList("WorkflowBOMMaintDoc", "WorkflowBOMMaint", para("BomName"), para("BomRev"), "RO", m_DataList,
                                                        "MaterialList", subEntityList, oldCount, result.Message)

        If result.Result Then
            '删除旧的，插入新的
            Dim exuteEntity = New ExcuteEntity("bomproductsubproduct", ExcuteEntity.ExcuteTypeEnum.del)


            Dim whereFileds = New List(Of FieldEntity)
            whereFileds.Add(New FieldEntity("productName", para("faProductName"), FieldEntity.FieldTypeEnum.Str))
            whereFileds.Add(New FieldEntity("productRev", para("faProductRev"), FieldEntity.FieldTypeEnum.Str))
            exuteEntity.WhereFileds = whereFileds

            DatabaseHelper.ExecuteDataByEntity(exuteEntity)
            DatabaseHelper.ExecuteSqlTran(bomSqlList)
        End If

        Return result

    End Function

#End Region

#Region "RDO对象获取"

    Public Function GetRDOInfo(ByVal tableName As String, ByVal value As String, ByVal rev As String) As DataTable
        Dim strSql = <string>select tb.{0}name,t.* from {0} t
               left join {0}base tb on tb.{0}baseid=t.{0}baseid
               where tb.{0}name='{1}'  
                </string>.Value
        strSql = String.Format(strSql, tableName, value)
        If Not String.IsNullOrWhiteSpace(rev) Then
            strSql += String.Format(" and t.{0}revision='{1}' ", tableName, rev)
        End If

        strSql += String.Format(" order by t.{0}id desc ", tableName)
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

#End Region

    ''' <summary>
    ''' 根据name和rev获取工序的信息
    ''' </summary>
    ''' <param name="name"></param>
    ''' <param name="rev"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepInfoByWorkflowNameRev(ByVal name As String, ByVal rev As String) As DataTable
        Dim strSql = <String>select wb.workflowname,w.workflowrevision,ws.sequence,ws.workflowstepname,s.specno,s.opdesc,ws.workflowstepid  from workflow w
               left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid
               left join workflowstep ws on ws.workflowid=w.workflowid
               left join specbase sb on sb.specbaseid=ws.specbaseid
               left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
               where wb.workflowname='{0}' 
             </String>.Value
        strSql = String.Format(strSql, name)
        If Not String.IsNullOrWhiteSpace(rev) Then
            strSql += String.Format(" and w.workflowrevision='{0}' ", rev)
        End If

        strSql += " order by ws.sequence "
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
     
    ''' <summary>
    ''' 工装保存批次工序相关的业务数据,用于工艺导入后
    ''' add:Wangjh 20230324
    ''' </summary>
    ''' <param name="contaienrName"></param> 
    ''' <param name="userInfo"></param> 
    ''' <remarks></remarks>
    Public Sub SaveConStepSomeInfo(ByVal contaienrName As String, ByVal stepDt As DataTable, ByVal userInfo As CurrentUserInfo)
        Dim containerId = DatabaseHelper.GetSingle("select containerid from container where containername='" + contaienrName + "'")
        If containerId Is Nothing Then
            Return
        End If
        Dim stepDalDt = DatabaseHelper.Query_ReturnDatTable(String.Format(<string>select ws.workflowstepname,ws.workflowstepid from workflow w
left join workflowstep ws on ws.workflowid=w.workflowid
left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid
where wb.workflowname='{0}' and w.workflowrevision='{1}'</string>.Value, stepDt.Rows(0)("WorkflowName").ToString, stepDt.Rows(0)("WorkflowRev").ToString))

        Dim sqlList As New ArrayList

        For Each row As DataRow In stepDt.Rows
            Dim stepDalRow = stepDalDt.Select("workflowstepname='" + row("specno").ToString().PadLeft(4, "0") + "-" + row("OpDesc").ToString() + "'").FirstOrDefault
            If stepDalRow Is Nothing Then
                Continue For
            End If
            '虚拟序处理，存入不加工记录 
            If row("OpDesc").ToString.Contains("虚拟序") Then
                sqlList.Add(String.Format("delete containerstepnoship css where css.CONTAINERID='{0}' and css.WORKFLOWSTEPID='{1}' and css.isuse=0 and css.noship = 2", containerId.ToString, stepDalRow("workflowstepid").ToString))
                sqlList.Add(String.Format(<string>INSERT INTO containerstepnoship (CONTAINERSTEPNOSHIPID,CONTAINERID,WORKFLOWSTEPID,ISUSE,NOSHIP,EMPLOYEEID,CREATEDATE) VALUES(sys_guid(),'{0}','{1}',0,2,'{2}',sysdate)</string>.Value, containerId.ToString, stepDalRow("workflowstepid").ToString, userInfo.EmployeeID))
            End If
            '备注处理，存入批次工序备注表
            If Not String.IsNullOrWhiteSpace(row("Notes").ToString) Then
                sqlList.Add(String.Format(<string>INSERT INTO containerstepattribute (CONTAINERSTEPATTRIBUTEID,CONTAINERID,WORKFLOWSTEPID,NOTES,CREATEEMPLOYEEID,CREATEDATE) VALUES(sys_guid(),'{0}','{1}','{2}','{3}',sysdate)</string>.Value,
                                          containerId.ToString, stepDalRow("workflowstepid").ToString, row("Notes").ToString, userInfo.EmployeeID))
            End If
        Next

        DatabaseHelper.ExecuteSqlTran(sqlList)


    End Sub
End Class
